﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.Common;
using System.Configuration;

/// <summary>
///DataHandler 的摘要说明
/// </summary>
public class DataHandler
{

    private static DataHandler _instance = null;
    private static object _synObj = new object();


    public static DataHandler Current
    {
        get
        {
            if (_instance == null)
            {
                lock (_synObj)
                {
                    if (_instance == null)
                    {
                        _instance = new DataHandler();
                        return _instance;
                    }
                }
            }

            return _instance;
        }
    }


    #region  Account
    public DataTable UserLogin(string userName, string password)
    {
        try
        {

            DBHelper dbHelper = new DBHelper();
            DbCommand cmd = dbHelper.GetStoredProcCommond("UserLog");
            dbHelper.AddInParameter(cmd, "@userName", DbType.String, userName);
            dbHelper.AddInParameter(cmd, "@password", DbType.String, Common.MD5Encrypt(string.Format("{0}{1}", password, ConfigurationManager.AppSettings["CONST_MD5_KEY"].ToString())));
            return dbHelper.ExecuteDataTable(cmd);
        }
        catch
        {
            return null;
        }

    }

    /// <summary>
    /// 注册新用户
    /// </summary>
    /// <param name="userName"></param>
    /// <param name="password"></param>
    /// <param name="birthday"></param>
    /// <param name="sex"></param>
    /// <param name="ssn"></param>
    /// <returns></returns>
    public int RegUser(string userName, string password, string birthday, int sex, string ssn)
    {
        try
        {
            DBHelper dbHelper = new DBHelper();
            DbCommand cmd = dbHelper.GetStoredProcCommond("RegisterUser");
            dbHelper.AddInParameter(cmd, "@userName", DbType.String, userName);
            dbHelper.AddInParameter(cmd, "@password", DbType.String, Common.MD5Encrypt(string.Format("{0}{1}", password, ConfigurationManager.AppSettings["CONST_MD5_KEY"].ToString())));

            dbHelper.AddInParameter(cmd, "@birthday", DbType.String, birthday);
            dbHelper.AddInParameter(cmd, "@sex", DbType.Int32, sex);
            dbHelper.AddInParameter(cmd, "@ssn", DbType.String, ssn);

            return dbHelper.ExecuteNonQuery(cmd);

        }
        catch
        {
            return -1;
        }
    }

    /// <summary>
    /// 修改密码
    /// </summary>
    /// <param name="uid"></param>
    /// <param name="password"></param>
    /// <param name="newPassword"></param>
    /// <returns></returns>
    public int ChangePwd(int uid, string password, string newPassword)
    {
        try
        {
            DBHelper dbHelper = new DBHelper();
            DbCommand cmd = dbHelper.GetStoredProcCommond("up_ChangePwd");
            dbHelper.AddInParameter(cmd, "@uid", DbType.Int32, uid);
            dbHelper.AddInParameter(cmd, "@oldPassword", DbType.String, Common.MD5Encrypt(string.Format("{0}{1}", password, ConfigurationManager.AppSettings["CONST_MD5_KEY"].ToString())));

            dbHelper.AddInParameter(cmd, "@newPwd", DbType.String, Common.MD5Encrypt(string.Format("{0}{1}", newPassword, ConfigurationManager.AppSettings["CONST_MD5_KEY"].ToString())));

            return dbHelper.ExecuteNonQuery(cmd);
        }
        catch
        {
            return -3; //系统错误
        }
    }


    /// <summary>
    /// 修改用户头像
    /// </summary>
    /// <param name="uid"></param>
    /// <param name="HreadImgURL"></param>
    /// <returns></returns>
    public int updHreadImg(int uid, string HreadImgURL)
    {
        try
        {
            DBHelper dbHelper = new DBHelper();
            DbCommand cmd = dbHelper.GetStoredProcCommond("up_UserHreadImg");
            dbHelper.AddInParameter(cmd, "@userID", DbType.Int32, uid);
            dbHelper.AddInParameter(cmd, "@hreadImg", DbType.String, HreadImgURL);

            return dbHelper.ExecuteNonQuery(cmd);
        }
        catch
        {
            return -1;
        }
    }





    /// <summary>
    /// 申请导游
    /// </summary>
    /// <param name="id"></param>
    /// <param name="title"></param>
    /// <param name="city"></param>
    /// <param name="Info"></param>
    /// <returns></returns>
    public int applyGuideInfo(int id, string title, string city, string Info)
    {
        try
        {
            DBHelper dbHelper = new DBHelper();
            DbCommand cmd = dbHelper.GetStoredProcCommond("applyGuide");
            dbHelper.AddInParameter(cmd, "@uid", DbType.Int32, id);
            dbHelper.AddInParameter(cmd, "@title", DbType.String, title);
            dbHelper.AddInParameter(cmd, "@city", DbType.String, city);
            dbHelper.AddInParameter(cmd, "@info", DbType.String, Info);

            return dbHelper.ExecuteNonQuery(cmd);
        }
        catch
        {
            return -1;
        }
    }

    #endregion

    #region Main

    /// <summary>
    /// 查询所欲导游信息
    /// </summary>
    /// <returns></returns>
    public DataTable GetAllGuide()
    {
        try
        {
            DBHelper dbHelper = new DBHelper();
            DbCommand cmd = dbHelper.GetStoredProcCommond("GetAllGuid");
            return dbHelper.ExecuteDataTable(cmd);
        }
        catch
        {
            return null;
        }
    }
    #endregion



    public DataTable GetGuiderInfoByid(int uid)
    {
        try
        {
            DBHelper dbHelper = new DBHelper();
            DbCommand cmd = dbHelper.GetStoredProcCommond("up_GetGuideInfobyID");
            dbHelper.AddInParameter(cmd, "@uid", DbType.Int16, uid);
            return dbHelper.ExecuteDataTable(cmd);
        }
        catch
        {
            return null;

        }
    }



    /// <summary>
    /// 创建订单
    /// </summary>
    /// <param name="uName"></param>
    /// <param name="uid"></param>
    /// <param name="roteId"></param>
    /// <param name="sDate"></param>
    /// <param name="eDate"></param>
    /// <param name="price"></param>
    /// <returns></returns>
    public int CreateOrder(string uName, int uid, int roteId, string sDate, string eDate, decimal price)
    {
        try
        {

            DBHelper dbHelper = new DBHelper();
            DbCommand cmd = dbHelper.GetStoredProcCommond("up_createOrders");
            dbHelper.AddInParameter(cmd, "@uName", DbType.String, uName);
            dbHelper.AddInParameter(cmd, "@Uid", DbType.Int32, uid);
            dbHelper.AddInParameter(cmd, "@RoteId", DbType.Int32, roteId);
            dbHelper.AddInParameter(cmd, "@bookingSDate", DbType.String, sDate);
            dbHelper.AddInParameter(cmd, "@bookingEDate", DbType.String, eDate);
            dbHelper.AddInParameter(cmd, "@Price", DbType.Decimal, price);

            return dbHelper.ExecuteNonQuery(cmd);
        }
        catch
        {
            return -1;
        }
    }

    public DataTable GetOrdersByid(int uid, int pageSize, int pageCount)
    {
        try
        {
            DBHelper dbHelper = new DBHelper();
            DbCommand cmd = dbHelper.GetStoredProcCommond("up_GetOrdersByuid");
            dbHelper.AddInParameter(cmd, "@uid", DbType.String, uid);
            dbHelper.AddInParameter(cmd, "@pageSize", DbType.Int32, pageSize);
            dbHelper.AddInParameter(cmd, "@pageCount", DbType.Int32, pageCount);

            return dbHelper.ExecuteDataTable(cmd);
        }
        catch
        {
            return null;
        }
    }

    /// <summary>
    /// 修改用户信息
    /// </summary>
    /// <param name="uid"></param>
    /// <param name="email"></param>
    /// <param name="guideNo"></param>
    /// <param name="isEnglish"></param>
    /// <param name="isLeave"></param>
    /// <returns></returns>
    public int upd_UserInfo(int uid, string email, string guideNo, int isEnglish, int isLeave)
    {
        try
        {
            DBHelper dbHelper = new DBHelper();
            DbCommand cmd = dbHelper.GetStoredProcCommond("up_GuideInfo");
            dbHelper.AddInParameter(cmd, "@uid", DbType.String, uid);
            dbHelper.AddInParameter(cmd, "@email", DbType.String, email);
            dbHelper.AddInParameter(cmd, "@guideNo", DbType.String, guideNo);
            dbHelper.AddInParameter(cmd, "@isEnglish", DbType.Int32, isEnglish);
            dbHelper.AddInParameter(cmd, "@isLeave", DbType.Int32, isLeave);

            return dbHelper.ExecuteNonQuery(cmd);
        }
        catch
        {
            return -1;
        }
    }


    /// <summary>
    /// 检查用户是否存在
    /// </summary>
    /// <param name="uName"></param>
    /// <returns></returns>
    public string checkAccountisCanUse(string uName)
    {
        try
        {
            DBHelper dbHelper = new DBHelper();
            DbCommand cmd = dbHelper.GetStoredProcCommond("up_CheckUserNameIsCanUse");
            dbHelper.AddInParameter(cmd, "@username", DbType.String, uName);

            DataTable dt  = dbHelper.ExecuteDataTable(cmd);
            if (dt != null && dt.Rows.Count > 0)
            {
                return dt.Rows[0]["ret"].ToString();
            }
            return "0";
        }
        catch
        {
            return "0";
        }
    }
}